package Dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Entity.MyDownload;
import OracleHelper.OracleHelper;

public class MyDownloadDao {
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:orcl", user = "c##university_resource", pwd = "20161946";
	Connection conn = null;
	CallableStatement cs = null;
	ResultSet rs = null;

	/* 向myDownload表中添加数据 */
	public int addMyDownload(MyDownload md) {
		/*
		 * String sql = "execute PKG_MYDOWNLOAD.PROC_MYDOWNLOAD_INSERT('" + md.getMyNo()
		 * + "','" + md.getResourceID() + "','" + md.getLabel() + "','" +
		 * md.getResourceName() + "'," + "'" + md.getUploaderID() + "','" +
		 * md.getUploadTime() + "','" + md.getBriefIntroduce() + "'";
		 */
		/*
		 * String
		 * sql="insert into myDownload values('"+md.getMyNo()+"','"+md.getResourceID()+
		 * "','"+md.getLabel()+"','"+md.getResourceName()+"'," +
		 * "'"+md.getUploaderID()+"','"+md.getUploadTime()+"','"+md.getBriefIntroduce()+
		 * "')";
		 */
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pwd);
			cs = conn.prepareCall("{call PKG_MYDOWNLOAD.PROC_MYDOWNLOAD_INSERT(?,?,?,?,?,?,?)}");
			cs.setString(1, md.getMyNo());
			cs.setString(2, md.getResourceID());
			cs.setString(3, md.getLabel());
			cs.setString(4, md.getResourceName());
			cs.setString(5, md.getUploaderID());
			cs.setString(6, md.getUploadTime());
			cs.setString(7, md.getBriefIntroduce());
			cs.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (cs != null) {
					cs.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
			}

		}
		return 1;
	}

	/* 根据课程号删除一条信息 */
	public int deleteMyDownloadByResourceId(String resourceID) {
		String sql = "delete from myDownload where resourceID='" + resourceID + "'";
		return new OracleHelper().executeUpdate(sql);

	}

	/* 根据学号查询该用户所下载的所有资源 */
	public List<MyDownload> queryAllDownloadByNo(String myNo) {
		List<MyDownload> mdlist = new ArrayList<MyDownload>();
		// String sql = "execute PKG_MYDOWNLOAD.PROC_MYDOWNLOAD_SHOWALL('" + myNo +
		// "')";
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, pwd);
			cs = conn.prepareCall("{call PKG_MYDOWNLOAD.PROC_MYDOWNLOAD_SHOWALL(?,?)}");
			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
			cs.setString(1, myNo);
			cs.execute();
			rs = (ResultSet) cs.getObject(2);

			while (rs.next()) {
				MyDownload mydownload = new MyDownload();
				mydownload.setMyNo(rs.getString(1));
				mydownload.setResourceID(rs.getString(2));
				mydownload.setLabel(rs.getString(3));
				mydownload.setResourceName(rs.getString(4));
				mydownload.setUploaderID(rs.getString(5));
				mydownload.setUploadTime(rs.getString(6));
				mydownload.setBriefIntroduce(rs.getString(7));
				mdlist.add(mydownload);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					if (cs != null) {
						cs.close();
					}
					if (conn != null) {
						conn.close();
					}
				}
			} catch (SQLException e) {
			}
		}
		return mdlist;

	}
}
